package com.yc.dao;


import java.io.InputStream;
import java.sql.*;
import java.util.*;


/**
 * 分装 工具类 数据库操作
 * @author 16643
 *
 */
public class DBHelper {
	/*private static String driverClass = "oracle.jdbc.driver.OracleDriver";
	private String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
	private String user = "scott";
	private String password = "a";*/

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	//静态块 只在类中第一次加载才执行且执行一次
	static {
		try {
			//String driverClass;
			//Class.forName(driverClass);
			Class.forName(ReadConfig.getInstance().getProperty("driverClassName"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	//建立连接
	private Connection getConn() {
		try {
			//conn = DriverManager.getConnection(url, user, password);
			conn = DriverManager.getConnection(ReadConfig.getInstance().getProperty("url"), ReadConfig.getInstance());
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 关闭资源
	 * @param rs 结果集
	 * @param pstmt 预编译语句块
	 * @param conn 连接
	 */

	private void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		//关闭资源 先开启的后关闭 后开启的先关闭    结果集->语句块->连接
		if(rs!=null) {
			try {
				rs.close();
			}catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if(pstmt != null) {
			try {
				pstmt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	/**
	 * 更新操作Object ... params 不定参数 类似于数组 万物皆对象
	 * @param sql 要执行的更新语句 可以insert update delete
	 * @param params 要执行的SQL语句中占位符所？需要的参数 一一对应
	 * @return
	 */
	public int update(String sql,Object ... params) {
		int result = 0;
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			result = pstmt.executeUpdate();//执行更新语句
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeAll(null, pstmt, conn);
		}
		return result;//返回更新语句受影响的行数
	}
	
	/**
	 * 多条SQL语句的更新操作
	 * @param sqls 要执行的多条更新语句 可以 insert update delete
	 * @param params 每一条SQL语句对应一组参数 list  多个list存入List中  要执行的SQL 语句中占位符? 需要的参数 一一对应
	 * @return
	 * @throws SQLException
	 */
	public int update(List<String> sqls, List<List<Object>> params) throws SQLException{
		int result = -1; // 定义默认返回值
		try {
			conn = this.getConn(); // 获取连接
			conn.setAutoCommit(false);
			// 循环迭代
			for(int i = 0, len = sqls.size(); i < len; ++ i) {
				System.out.println(i);
				pstmt = conn.prepareStatement(sqls.get(i)); // 编译语句块装载SQL 每次执行一个SQL
				this.setParams(pstmt,  params.get(i).toArray());
				result = pstmt.executeUpdate(); // 执行更新语句
			} 
			// 所有的SQL语句更新成功 提交事务
			conn.commit();
		}catch(Exception e) {
			e.printStackTrace();
			conn.rollback(); // 事务回滚
		}finally {
			conn.setAutoCommit(true); // 将事物的提交方式设为自动
			closeAll(null,  pstmt, conn);
		}
		
		return result;
	}
	
	


	/**
	 * 给预编译语句块中的占位符？赋值 设置参数
	 * @param pstmt
	 * @param params
	 */
	private void setParams(PreparedStatement pstmt,Object ... params) {
		if(null == params || params.length <= 0) {
			return;
		}

		for (int i =0,len =params.length;i < len;i++) {
			try {
				pstmt.setObject(i+1, params[i]);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
				//项目建议存入日志 暂时打印
				System.out.println("第"+ (i+1) + "个参数注入失败");
			}
		}
	}
	
	/**
	 * 单行查询返回一条记录select*from userinfo where user_id = ?
	 * @param sql
	 * @param params
	 * @return
	 */
	public Map<String, Object> findSingle(String sql,Object ...params){
		Map<String, Object> map = null;
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			List<String> columnNames = this.getAllColumnNames(rs);
			if(rs.next()) {

				map = new HashMap<String, Object>();
				/*map.put("user_id",rs.getInt("user_id"));
				map.put("user_name",rs.getString("user_name"));
				map.put("user_pwd",rs.getString("user_pwd"));*/
				for(String columnName : columnNames) {
					map.put(columnName, rs.getObject(columnName));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();

		}finally {
			closeAll(rs, pstmt, conn);
		}
		return map;
	}
	/**
	 * JBDC2.0获取所有的列名
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<String> getAllColumnNames(ResultSet rs){
		List<String> columnNames = new ArrayList<String>();
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			for(int i = 1; i <= count; i++) {
				columnNames.add(rsmd.getColumnName(i).toLowerCase());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return columnNames;
	}
	/**
	 * 多行查询 返回一条记录 select * from userinfo
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>>findMutile(String sql, Object ... params){
		List<Map<String, Object>>list = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = null;
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();

			List<String> columnNames = this.getAllColumnNames(rs);
			Object value = null; // 数据库中对应列的值
			String type = ""; // 数据库中列对应的数据类型
			while(rs.next()) {
				map = new HashMap<String, Object>();

				for(String columnName : columnNames) {
					value = rs.getObject(columnName); // 数据库中列对应值的数据类型
					if(value == null) {
						map.put(columnName, value);
						continue;
					}
					type = value.getClass().getName();
					
					if("oracle.sql.BLOB".equals(type)) {
						Blob blob = rs.getBlob(columnName);
						try(InputStream in = blob.getBinaryStream()) {
							byte []  bt =new byte[(int)blob.length()];
							in.read(bt);
							map.put(columnName, bt);
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
					}else {
						map.put(columnName, value);
					}
				}
				list.add(map);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			closeAll(rs, pstmt, conn);
		}
		return list;
	}
}

